Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Extending the sample window to use the queries

Now you can try out some of the things you’ve learned about queries.

To extend the sample application window to use some query statements:

Open the procedure h-CustOrderWin1.w.

This gives you the test procedure with Customer fields and the Order browse but without some of the other graphical objects you added later.

Extend the window somewhat to the right, then drop two buttons onto the window.
Name the first button PosButton and give it a label of Save Position.
Name the second button ReposButton and give it a label of Restore Position.
Open the Section Editor and select the Definitions section.
Define a variable to hold a value for the Customer query’s CURRENT-RESULT-ROW:

/* Local Variable Definitions --- */ DEFINE VARIABLE iQueryRow AS INTEGER     NO-UNDO.

Remember that the Definitions section is scoped to the entire procedure file, so anything you define here is available to any trigger or internal procedure inside it.

Go into the Triggers section for the PosButton and give it this CHOOSE trigger:

DO:   iQueryRow = CURRENT-RESULT-ROW('CustQuery'). END.

Define this CHOOSE trigger for the ReposButton:

DO:     REPOSITION CustQuery TO ROW iQueryRow.     APPLY "CHOOSE" TO BtnNext. END.

When you click the Save Position button, your code saves off the current row number from the results list. You can then move around in the query. When you press the Restore Position button, the Customer query is repositioned to the row you saved, and the Order query is opened for that Customer.

Why did you need the APPLY “CHOOSE” TO BtnNext ? statement. Remember that when you use the REPOSITION statement Progress positions before the record you want, so you need to execute a GET NEXT to make it available. At the same time, in this case, your code needs to reopen the dependent Order query for the Customer as well. All this is done by the trigger code on the Next button.

Run the procedure and test saving off and restoring the current row.
Add a fill-in field to give you a reason to test reopening the query with a different WHERE clause:
Drop a fill-in on the window. Call it cState and give it a label of New State.
Write this LEAVE trigger for the fill-in field in the Section Editor:

DO:     OPEN QUERY CustQuery FOR EACH Customer WHERE State =         cState:SCREEN-VALUE.     APPLY "CHOOSE" TO BtnFirst. END.

Each time you enter a state abbreviation in the field and tab out of it, the trigger fires and the query is reopened with that new state. Remember that it isn’t necessary to close a query explicitly if you are immediately going to reopen it, so a CLOSE QUERY CustQuery statement here is optional. Also remember that unless you specifically assign it using the ASSIGN statement, the value the user types into the fill-in exists only in the frame’s screen buffer, so you can retrieve it using the SCREEN-VALUE attribute of the field.

Similarly for the CHOOSE trigger that repositions to the previously saved row, you need to apply CHOOSE to the First button trigger to get the first row for the new query and reopen the Order query.

Run your procedure. You can enter a state name and tab out of the New State field and see the Customers in that state along with their Orders:



If you enter an invalid state name, the procedure doesn’t give you any feedback to confirm this. In the next section, you make a few more changes to the procedure to check whether there were any results for the state that is entered. This makes use of the NUM-RESULTS function and also introduces you to another new and useful 4GL statement.

Using NUM-RESULTS to check the validity of the query

The query is sorted by the City, which is a nonindexed field. Therefore, the NUM-RESULTS function returns the total number of records that satisfy the query as soon as it is opened, because they all have to be retrieved before they can be sorted.

Next, you add another field to the screen to display that number, and then check the same value in the trigger for the New State field to make sure that the state entered was valid

To add another field to the screen:

Drop another fill-in field onto the window. Name it iMatches and give it a Label of Number of Matches.

To display initial values for the New State and Number of Matches fields, you’ll put some code into the Main Block of the procedure, to be executed right after the query is initially opened.

In the Section Editor, select the Main Block and add this code after the RUN enable_UI statement:

DO ON ERROR    UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK     ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:     RUN enable_UI.   ASSIGN cState = Customer.State         iMatches = NUM-RESULTS("CustQuery").   DISPLAY cState iMatches WITH FRAME CustQuery. END.

This displays the initial value of the Customer State (“NH”) along with the number of Customers in New Hampshire. Remember that, in this case, CustQuery is both the name of the query and also the name of the frame the AppBuilder created for you.

Edit the LEAVE trigger for the New State field again. Add a statement before the OPEN QUERY statement to save off the current State in the cState field, and a statement following the OPEN QUERY statement to retrieve the NUM-RESULTS value for the new query:

cState = Customer.State.   OPEN QUERY CustQuery FOR EACH Customer WHERE Customer.State =        cState:SCREEN-VALUE BY Customer.City.        /* Check whether the State was valid or not. */   iMatches = NUM-RESULTS("CustQuery").

Remember that the value you assign to cState is not overwritten by the SCREEN-VALUE that you type into the field on the screen, so you can open the query based on the SCREEN-VALUE and still keep the old value around if you need it later (which you will).

Using the MESSAGE statement

Next you need to display a warning message if there are no results, which means that no Customer records match the state value that was entered. Progress has a MESSAGE statement to display a message to the screen or to a message area at the bottom of the window if there is one. A MESSAGE statement can contain one or more character expressions (quoted literals or CHARACTER variables) that make up the message. If there are multiple expressions in the statement, Progress simply concatenates them all, with a single space between them. You can also insert the SKIP keyword anywhere in the message to skip a line, or SKIP(n) to skip n lines.

If you want the message to appear in its own alert box, you can include the phrase VIEW-AS ALERT-BOX in the statement. This is the default if the message is displayed from a GUI window. If you want to give the message alert box a special format, you can include one of the QUESTION, INFORMATION, ERROR, or WARNING keywords after the VIEW-AS ALERT-BOX phrase. The MESSAGE statement has other features, including the ability to display different sets of buttons, beyond just an OK button to acknowledge the message. It can also capture an answer from the message and store it in a variable. You can see all the syntax details in OpenEdge Development: Progress 4GL Reference or in the online help topic for the MESSAGE statement.

To display a warning message that tells you that there are no matching Customers:

In the New State trigger, add this code:

IF iMatches = 0 THEN   DO:       MESSAGE "There are no Customers that match the State"           cState:SCREEN-VALUE "." SKIP           "Restoring the previous State."           VIEW-AS ALERT-BOX WARNING.

Reopen the query using the value of the State field that you saved off in the cState variable and redisplay the previous valid New State value:

/* Reopen the query with the previous state. */       OPEN QUERY CustQuery FOR EACH Customer WHERE Customer.State =       cState BY Customer.City.       /* Display the previous valid state as well. */       DISPLAY cState WITH FRAME CustQuery.   END.

This ends the DO block that is executed if NUM-RESULTS is zero.

To display the number of matching Customers if NUM-RESULTS is not zero, add the following code:

ELSE DISPLAY iMatches WITH FRAME CustQuery.

Add the following code so that, regardless of whether the New State was valid, the statement executes APPLY “CHOOSE” to the First button to do a GET FIRST and opens the Order query:

APPLY "CHOOSE" TO BtnFirst.

Save the procedure as h-CustOrderWin4.w.
Run the procedure to see the number of matching records for any State you enter:



To see your warning message, enter an invalid State value, such as QQ: